﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;

namespace DAO
{
    public class PhongDAO
    {
        public static DataTable GetAll()
        {
            DataProvider dp = new DataProvider();
            string sql = "Select p.MaPhong, p.TenPhong, lp.TenLoaiPhong, lp.DonGia, p.GhiChu, p.TinhTrangPhong, p.MaLoaiPhong From Phong p, LoaiPhong lp where p.MaLoaiPhong = lp.MaLoaiPhong";
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable GetTinhTrang()
        {
            DataProvider dp = new DataProvider();
            string sql = "Select TinhTrangPhong From Phong Group by TinhTrangPhong";
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable PhongTheoMa(int maphong)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select p.MaPhong, p.TenPhong, p.MaLoaiPhong, lp.TenLoaiPhong, lp.DonGia, p.GhiChu, p.TinhTrangPhong From Phong p, LoaiPhong lp where p.MaLoaiPhong=lp.MaLoaiPhong and p.MaPhong = " + maphong + "");
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable PhongTheoMaLoaiPhong(int maloaiphong)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select * From Phong where MaLoaiPhong= " + maloaiphong);
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable PhongTheoTen(string tenphong)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select MaPhong From Phong Where TenPhong = '" + tenphong + "'");
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static DataTable PhongTheoLoai(string loai)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select * From LoaiPhong l, Phong p Where l.MaLoaiPhong=p.MaLoaiPhong and TenLoaiPhong like '" + loai + "' and TinhtrangPhong=1");
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static void ThemPhong(PhongDTO p)
        {
            string sql = string.Format("INSERT INTO Phong (MaPhong, TenPhong, MaLoaiPhong, GhiChu, TinhTrangPhong) VALUES (" + p.MaPhong + ",'" + p.TenPhong + "','" + p.MaLoaiPhong + "','" + p.GhiChu + "', " + p.TinhTrangPhong + ")");
            DataProvider.ExecuteNonQuery(sql);
        }

        public static void CapNhatPhong(PhongDTO phong)
        {
            string sql = string.Format("Update Phong Set TenPhong = N'" + phong.TenPhong + "', MaLoaiPhong = " + phong.MaLoaiPhong + ", GhiChu = N'" + phong.GhiChu + "', TinhTrangPhong = " + phong.TinhTrangPhong + " Where MaPhong = " + phong.MaPhong + "");
            DataProvider.ExecuteNonQuery(sql);
        }

        public static void XoaPhong(int maphong)
        {
            string sql = string.Format("Delete From Phong Where MaPhong = " + maphong + "");
            DataProvider.ExecuteNonQuery(sql);
        }

        public static DataTable TimPhong(string maphong, string tenphong, string maloaiphong, string loaiphong, string tinhtrang, double giatu, double giaden)
        {
            DataProvider dp = new DataProvider();
            string sql = string.Format("Select p.MaPhong, p.TenPhong, lp.TenLoaiPhong, lp.DonGia, p.TinhTrangPhong  From Phong p, LoaiPhong lp where p.MaLoaiPhong=lp.MaLoaiPhong and p.MaPhong like '" + maphong + "' and p.TenPhong like '" + tenphong + "' and lp.MaLoaiPhong like '" + maloaiphong + "' and lp.TenLoaiPhong like '" + loaiphong + "' and p.TinhTrangPhong like '" + tinhtrang + "' and lp.DonGia between " + giatu + " and " + giaden + "");
            DataTable dt = dp.ExecuteQuery(sql);
            return dt;
        }

        public static void CapNhatTinhTrangPhong(int maphong, int tinhtrang)
        {
            string sql = string.Format("Update Phong Set TinhTrangPhong = " + tinhtrang + " Where MaPhong = " + maphong + "");
            DataProvider.ExecuteNonQuery(sql);
        }

    }
}
